SET NOCOUNT OFF
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

/******************************************************************************/
/* Step 1. Remove stored procedure if it already exists                       */
/******************************************************************************/
IF (SELECT OBJECT_ID('[CaseManagement].[VisitsForCase]')) IS NOT NULL
BEGIN
  DROP PROCEDURE [CaseManagement].[VisitsForCase]
END

GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE [CaseManagement].[VisitsForCase]( @CaseOID BIGINT )
AS  
BEGIN

	Select  Visits.OID as 'VisitOID',
            Visits.Version as 'VisitVersion',
            Visits.CaseID,
            Visits.ArrivalDate as 'VisitArrivalDate',
            Visits.DischargeDate as 'VisitDischargeDate',
            Visits.FacilityID as 'VisitFacilityID',
            Visits.EstimatedLOS as 'VisitEstimatedLOS',
            Visits.FamilyPresent as 'VisitFamilyPresent',
            Visits.FamilyArrivalDate as 'VisitFamilyArrivalDate',
            Visits.FamilyDepartureDate as 'VisitFamilyDepartureDate',
            Visits.FamilyEstimatedLOS as 'VisitFamilyEstimatedLOS',
            Visits.MethodOfTravel as 'VisitMethodOfTravel',
            Visits.OriginOfTravel as 'VisitOriginOfTravel',
            Visits.InvitationalTravelOrders as 'VisitInvitationalTravelOrders',
            Visits.UpdatedDate as 'VisitUpdatedDate',
            Visits.CreatedDate as 'VisitCreatedDate'
	From Visits
	Where
		Visits.CaseID = @CaseOID

END 
GO

/******************************************************************************/
/* Step 3. Assign appropriate permissions                                     */
/******************************************************************************/
GRANT EXECUTE ON [CaseManagement].[VisitsForCase] TO [Liberty_Users]
GO
-- Developers and Admins have permissions to execute through membership 
